import logging
import math
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

my_course_plan_router = APIRouter(prefix="/school/CoursePlan", tags=["课程计划管理"],
                                  dependencies=[Depends(oauth2_scheme)])


class CoursePlanSearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    fyear: Optional[str] = Field("", description="计划年份")
    fnote: Optional[str] = Field("", description="计划备注(支持前缀匹配)")


@my_course_plan_router.get("/list", summary="课程计划查询接口")
def list_course_plan(search: CoursePlanSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from a_ts_courseplanh where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fyear != "":
        sql = sql + " and fyear = :fyear "
        param["fyear"] = search.fyear
    if search.fnote != "":
        sql = sql + " and fnote like concat('%', :fnote ,'%')"
        param["fnote"] = search.fnote
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fbillno,fsite_no,fyear,fbegdate,fenddate,"
    sql = sql + " fnote,fmemo,fstate,fupddate,foperator"
    sql = sql + " from a_ts_courseplanh where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fyear != "":
        sql = sql + " and fyear = :fyear "
        param["fyear"] = search.fyear
    if search.fnote != "":
        sql = sql + " and fnote like concat('%', :fnote ,'%')"
        param["fnote"] = search.fnote
    sql = sql + " limit :limit offset :offset "
    param["offset"] = search.offset
    param["limit"] = search.limit
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['fsiteno'] = data[key][1]
        d1['fyear'] = data[key][2]
        d1['fbegdate'] = data[key][3]
        d1['fenddate'] = data[key][4]
        d1['fnote'] = data[key][5]
        d1['fmemo'] = data[key][6]
        d1['fstate'] = data[key][7]
        d1['fupddate'] = data[key][8]
        d1['foperator'] = data[key][9]
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)
